package bcared;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ServiceOrder {

	// variables directly stored in the ServiceOrders table
	private int SOID;
	private int priorityID;
	private int soStatusID;
	private int serviceOrderTypeID;
	private int equipmentID;
	private int customerID;
	private String openDate;
	private String closeDate;
	private String contactName;
	private String contactPhone;
	private String contactEmail;
	private int timeEstimate;
	private String problemDesc;
	private String resolutionNotes;
	private int lifeInDanger;

	// variables that are useful but from other tables
	private String priorityDescription;
	private String SOStatusDesc;
	private String serialNumber;
	private String serviceTypeDescription;
	private String productName;
	private String address1;
	private String address2;
	private String city;
	private String state;
	private String zip;
	private String customerName;
	private int modalityID;
	
	
	//TODO list all the check ins/outs
	//array list of all check ins/outs associated with this serviceorder
	private ArrayList<CheckInOut> checks = new ArrayList<CheckInOut>();

	private ResultSet rs = null;
	private CallableStatement csmt = null;
	private PreparedStatement ps = null;
	private OraDB myDB = new OraDB();

	public ServiceOrder() {
		
	}
	
	public ServiceOrder(int SOID) throws SQLException, ClassNotFoundException,
			InstantiationException, IllegalAccessException {
		this.SOID = SOID;
		populate();
	}

	public void populate() throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select SOID, ServiceOrders.priorityID, ServiceOrders.soStatusID, ServiceOrders.serviceOrderTypeID, ServiceOrders.equipmentID, "
				+ " TO_CHAR(openDate, 'YYYY-MM-DD') as openDate, TO_CHAR(closeDate, 'YYYY-MM-DD') as closeDate,"
				+ " ServiceOrders.contactName, ServiceOrders.contactPhone, ServiceOrders.contactEmail, ServiceOrders.timeEstimate, modalityID,"
				+ " problemDesc, resolutionNotes, lifeInDanger, priorityDescription, sostatusdesc, serialnumber, servicetypedescription,"
				+ " productname, Customer.customerID, Customer.customerName, Location.address1, Location.address2, Location.city, Location.state, Location.zip"
				+ " FROM serviceorders"
				+ " INNER JOIN Customer ON Customer.customerID = ServiceOrders.customerID"
				+ " INNER JOIN Equipment ON Equipment.equipmentID = ServiceOrders.equipmentID"
				+ " INNER JOIN Product ON Product.productID = Equipment.productID"
				+ " INNER JOIN Status ON Status.statusID = Equipment.statusID"
				+ " INNER JOIN ServiceOrderStatus ON ServiceOrderStatus.SOStatusID = ServiceOrders.SOStatusID"
				+ " INNER JOIN Location ON Location.locationID = Equipment.locationID"
				+ " INNER JOIN PriorityDescription ON PriorityDescription.priorityID = serviceorders.priorityID"
				+ " INNER JOIN ServiceOrderType ON ServiceOrderType.ServiceOrderTypeID = ServiceOrders.ServiceOrderTypeID"
				+ " where SOID = ?";

		this.ps = myDB.conn.prepareStatement(myQuery);
		this.ps.setInt(1, SOID);
		this.rs = this.ps.executeQuery();

		while (rs.next()) {
			this.priorityID = rs.getInt("priorityID");
			this.soStatusID = rs.getInt("sostatusID");
			this.serviceOrderTypeID = rs.getInt("serviceOrderTypeID");
			this.equipmentID = rs.getInt("equipmentID");
			this.customerID = rs.getInt("customerID");
			this.openDate = rs.getString("openDate");
			this.closeDate = rs.getString("closeDate");
			this.contactName = rs.getString("contactName");
			this.contactPhone = rs.getString("contactPhone");
			this.contactEmail = rs.getString("contactEmail");
			this.timeEstimate = rs.getInt("timeEstimate");
			this.modalityID = rs.getInt("modalityID");
			this.problemDesc = rs.getString("problemDesc");
			this.resolutionNotes = rs.getString("resolutionNotes");
			this.lifeInDanger = rs.getInt("lifeInDanger");
			this.priorityDescription = rs.getString("priorityDescription");
			this.SOStatusDesc = rs.getString("SOStatusDesc");
			this.serialNumber = rs.getString("serialNumber");
			this.serviceTypeDescription = rs.getString("serviceTypeDescription");
			this.productName = rs.getString("productName");
			this.address1 = rs.getString("address1");
			this.address2 = rs.getString("address2");
			this.city = rs.getString("city");
			this.state = rs.getString("state");
			this.zip = rs.getString("zip");
			this.customerName = rs.getString("customername");
			
		}
		
		myQuery = "select checkinout.checkID"
				+ " from serviceorders"
				+ " INNER JOIN serviceorderscheck on serviceorderscheck.soid = serviceorders.soid"
				+ " INNER JOIN checkinout on serviceorderscheck.checkid = checkinout.checkid"
				+ " where serviceorders.soid = ? and type = 2";
		
		this.ps = myDB.conn.prepareStatement(myQuery);
		this.ps.setInt(1, SOID);
		this.rs = this.ps.executeQuery();
		
		while (rs.next()) {
			this.checks.add(new CheckInOut(rs.getInt("checkID")));
		}
		//TODO verify that this works and populates checks;
		myDB.closeConnection();
	}

	public ArrayList<CheckInOut> getChecks() {
		return checks;
	}

	public void update() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		//String result = "";
		String myQuery = "call SOUpdate(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		csmt = myDB.conn.prepareCall(myQuery);
		
		csmt.setInt(1, this.SOID);
		csmt.setInt(2, this.priorityID);
		csmt.setInt(3, this.soStatusID);
		csmt.setInt(4, this.serviceOrderTypeID);
		csmt.setInt(5, this.equipmentID);
		csmt.setInt(6, this.customerID);
		csmt.setString(7, this.openDate);
		csmt.setString(8, this.closeDate);
		csmt.setString(9, this.contactName);
		csmt.setString(10, this.contactPhone);
		csmt.setString(11, this.contactEmail);
		csmt.setInt(12, this.timeEstimate);
		csmt.setString(13, this.problemDesc);
		csmt.setString(14, this.resolutionNotes);
		csmt.setInt(15, this.lifeInDanger);
		
		csmt.execute();
		myDB.closeConnection();
		
		populate();
	}
	
	public void insert(int priorityID, int soStatusID, int serviceOrderTypeID, int equipmentID,
			int customerID, String contactName, String contactPhone,
			String contactEmail, String problemDesc, int lifeInDanger)
			throws SQLException, ClassNotFoundException,
			InstantiationException, IllegalAccessException {

		myDB.createConnection();
		
		String myQuery = "INSERT INTO ServiceOrders (SOID, priorityID, soStatusID, serviceOrderTypeID, equipmentID," +
													" customerID, openDate, closeDate, contactName, contactPhone," +
													" contactEmail, timeEstimate, problemDesc, resolutionNotes," +
													" lifeInDanger)"
					 + " VALUES((select (max(SOID) + 1) as SOID from ServiceOrders), ?, ?, ?, ?, ?, current_date, NULL, ?, ?, ?, ?, ?, ?, ?)";
	
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, priorityID);
		ps.setInt(2, soStatusID); //soStatusID (waiting to be assigned)
		ps.setInt(3, serviceOrderTypeID);
		ps.setInt(4, equipmentID);
		ps.setInt(5, customerID);
		ps.setString(6, contactName);
		ps.setString(7, contactPhone);
		ps.setString(8, contactEmail);
		ps.setInt(9, -1);
		ps.setString(10, problemDesc);
		ps.setString(11, "");
		ps.setInt(12, lifeInDanger);
		rs = ps.executeQuery();
		myDB.closeConnection();
		Equipment e = new Equipment(equipmentID);
		e.setStatusID(3);
	}

	public void setSOID(int soid) {
		SOID = soid;
	}

	public void setPriorityID(int priorityID) {
		this.priorityID = priorityID;
	}

	public void setSoStatusID(int soStatusID) {
		this.soStatusID = soStatusID;
	}

	public void setServiceOrderTypeID(int serviceOrderTypeID) {
		this.serviceOrderTypeID = serviceOrderTypeID;
	}

	public void setEquipmentID(int equipmentID) {
		this.equipmentID = equipmentID;
	}

	public void setCustomerID(int customerID) {
		this.customerID = customerID;
	}

	public void setOpenDate(String openDate) {
		this.openDate = openDate;
	}

	public void setCloseDate(String closeDate) {
		this.closeDate = closeDate;
	}

	public void setContactName(String contactName) {
		this.contactName = contactName;
	}

	public void setContactPhone(String contactPhone) {
		this.contactPhone = contactPhone;
	}

	public void setContactEmail(String contactEmail) {
		this.contactEmail = contactEmail;
	}

	public void setTimeEstimate(int timeEstimate) {
		this.timeEstimate = timeEstimate;
	}

	public void setProblemDesc(String problemDesc) {
		this.problemDesc = problemDesc;
	}

	public void setResolutionNotes(String resolutionNotes) {
		this.resolutionNotes = resolutionNotes;
	}

	public void setLifeInDanger(int lifeInDanger) {
		this.lifeInDanger = lifeInDanger;
	}

	public int getSOID() {
		return SOID;
	}

	public int getPriorityID() {
		return priorityID;
	}

	public int getSoStatusID() {
		return soStatusID;
	}

	public int getServiceOrderTypeID() {
		return serviceOrderTypeID;
	}

	public int getEquipmentID() {
		return equipmentID;
	}

	public int getCustomerID() {
		return customerID;
	}

	public String getOpenDate() {
		return openDate;
	}

	public String getCloseDate() {
		if (closeDate == null) {
			return "";
		}
		return closeDate;
	}

	public String getContactName() {
		return contactName;
	}

	public String getContactPhone() {
		return contactPhone;
	}

	public String getContactEmail() {
		return contactEmail;
	}

	public int getTimeEstimate() {
		return timeEstimate;
	}

	public String getProblemDesc() {
		return problemDesc;
	}

	public String getResolutionNotes() {
		return resolutionNotes;
	}
	
	public int getModalityID() {
		return modalityID;
	}

	public int getLifeInDanger() {
		return lifeInDanger;
	}

	public String getPriorityDescription() {
		return priorityDescription;
	}

	public String getSOStatusDesc() {
		return SOStatusDesc;
	}

	public String getSerialNumber() {
		return serialNumber;
	}

	public String getServiceTypeDescription() {
		return serviceTypeDescription;
	}

	public String getProductName() {
		return productName;
	}

	public String getAddress1() {
		return address1;
	}

	public String getAddress2() {
		if (address2 == null) {
			return "";
		}
		return address2;
	}

	public String getCity() {
		return city;
	}

	public String getState() {
		return state;
	}

	public String getZip() {
		return zip;
	}

	public String getCustomerName() {
		return customerName;
	}

	public void closeConnection() throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {
		myDB.closeConnection();
	}
	
	public ResultSet getSOType()
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {

	myDB.createConnection();

	String myQuery = "select serviceordertypeid, servicetypedescription " +
	"from serviceordertype " +
	"order by servicetypedescription";

	ps = myDB.conn.prepareStatement(myQuery);
	rs = ps.executeQuery();
	return rs;
	}
	
}
